using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using MyClever.Lib;

namespace MyClever.DefaultPlugins
{
    public class WriteMySQL : MissionPlugin
    {
        [Package(Direction.Input, "Defines the setting to get access to the MySQL database.")]
        enum MySQLCredentials
        {
            [Column(typeof(string), "The host, you like read from.")]
            Host,
            [Column(typeof(string), "The database, you like to read from.")]
            DatabaseName,
            [Column(typeof(string), "The user name to get access to the database.")]
            UserName,
            [Column(typeof(string), "The user password to get access to the database.")]
            UserPassword
        }

        [Package(Direction.Input, "Defines the setting to create the MySQL database file.")]
        enum MySQLQuery
        {
            [Column(typeof(string), "Defines the table you like to access.")]
            TableName,
            [Column(typeof(bool), "If true, the new table supports an integer primary key ID.")]
            AddPrimaryKey,
            [Column(typeof(string), "Is given, the new table supports a column which saves the date of every row creation.")]
            CreateAtColumnName
        }

        [Package(Direction.Input, "Defines the data, which should written in the database.")]
        enum DBData { }

        [Plugin(PluginDevStatus.Production, "Write data in your favorite table of your expected MySQL database file. If the table is not existing, the plugin creates the expected table.")]
        public WriteMySQL()
        {
        }

        public override void Initialize()
        {
            
            //This is the first Function called by the host...
            //Put anything needed to start with here first
        }

        public override void Dispose()
        {
            //Put any cleanup code in here for when the program is stopped
        }

        /// <summary>
        /// Generate new Source-content.
        /// </summary>
        protected override void Work()
        {            
            DataTableReader mySQLCredentialsPackage = this.GetDataReader(typeof(MySQLCredentials));
            while (mySQLCredentialsPackage.Read())
            {
                this.AddNewLoggingMessage(typeof(MySQLCredentials), String.Format("connect to mysql database: '{0}' from host: {1}", mySQLCredentialsPackage[MySQLCredentials.DatabaseName.ToString()].ToString(), mySQLCredentialsPackage[MySQLCredentials.Host.ToString()].ToString()));

                string connectionString = String.Format("Server={0};Uid={1};Pwd={2};Database={3};", mySQLCredentialsPackage[MySQLCredentials.Host.ToString()].ToString(), mySQLCredentialsPackage[MySQLCredentials.UserName.ToString()].ToString(), mySQLCredentialsPackage[MySQLCredentials.UserPassword.ToString()].ToString(), mySQLCredentialsPackage[MySQLCredentials.DatabaseName.ToString()].ToString());

                MySqlConnection connection = new MySqlConnection(@connectionString);
                connection.Open();

                try
                {
                    DataTableReader mySQLQueryPackage = this.GetDataReader(typeof(MySQLQuery));
                    while (mySQLQueryPackage.Read())
                    {
                        this.AddNewLoggingMessage(typeof(MySQLQuery), String.Format("write table: '{0}'", mySQLQueryPackage[MySQLQuery.TableName.ToString()].ToString()));

                        // create table id not exisiting
                        StringBuilder commandTextBuilder = new StringBuilder();
                        commandTextBuilder.Append("CREATE TABLE IF NOT EXISTS " + mySQLQueryPackage[MySQLQuery.TableName.ToString()].ToString());
                        commandTextBuilder.Append("( ");

                        if (true == Convert.ToBoolean(mySQLQueryPackage[MySQLQuery.AddPrimaryKey.ToString()]))
                        {
                            commandTextBuilder.Append("fld_id INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, ");
                        }

                        if (false == String.IsNullOrEmpty(mySQLQueryPackage[MySQLQuery.CreateAtColumnName.ToString()].ToString()))
                        {
                            commandTextBuilder.AppendFormat("{0} TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, ", mySQLQueryPackage[MySQLQuery.CreateAtColumnName.ToString()].ToString());
                        }

                        DataTableReader dbDataPackage = this.GetDataReader(typeof(DBData));
                        commandTextBuilder.Append(this.GetSQLColumnDefinitions(this.GetColumnNames(dbDataPackage)));
                        commandTextBuilder.Append(");");

                        // is there a valid create-table-query?
                        if (commandTextBuilder.Length > 0)
                        {
                            MySqlCommand createCommand = new MySqlCommand(commandTextBuilder.ToString(), connection);
                            createCommand.ExecuteNonQuery();
                            createCommand.Dispose();
                            createCommand = null;

                            commandTextBuilder.Remove(0, commandTextBuilder.Length);
                        }

                        // write content to the table
                        while (dbDataPackage.Read())
                        {
                            commandTextBuilder.Append("INSERT INTO " + mySQLQueryPackage[MySQLQuery.TableName.ToString()].ToString() + " (");
                            foreach (KeyValuePair<string, Type> columnOn in this.GetColumnNames(dbDataPackage))
                            {
                                commandTextBuilder.Append(columnOn.Key.Replace(' ', '_') + ",");
                            }
                            commandTextBuilder.Remove(commandTextBuilder.Length - 1, 1);
                            commandTextBuilder.Append(") VALUES(");

                            foreach (KeyValuePair<string, Type> columnOn in this.GetColumnNames(dbDataPackage))
                            {
                                commandTextBuilder.Append("'");
                                commandTextBuilder.Append(dbDataPackage[columnOn.Key].ToString().Replace("'", "\""));
                                commandTextBuilder.Append("',");
                            }
                            commandTextBuilder.Remove(commandTextBuilder.Length - 1, 1);
                            commandTextBuilder.Append("); ");
                        }

                        // is there a valid create-table-query?
                        if (commandTextBuilder.Length > 0)
                        {
                            MySqlCommand insertCommand = new MySqlCommand(commandTextBuilder.ToString(), connection);
                            insertCommand.CommandText = commandTextBuilder.ToString();
                            insertCommand.ExecuteNonQuery();
                            insertCommand.Dispose();
                            insertCommand = null;

                            commandTextBuilder.Remove(0, commandTextBuilder.Length);
                        }
                    }

                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                    connection = null;
                }
            }            
        }

        private string GetSQLColumnDefinitions(List<KeyValuePair<string, Type>> columns)
        {            
            string returnValue = "";
            foreach (KeyValuePair<string, Type> columnOn in columns)
	        {
                returnValue += columnOn.Key.Replace(' ', '_') +" " + GetSQLColumnType(columnOn.Value) + ","; 
	        }
            return returnValue.Substring(0, returnValue.Length - 1);
        }

        private string GetSQLColumnType(Type columnType)
        {
            if (typeof(Int16) == columnType || typeof(Int32) == columnType || typeof(Int64) == columnType
                || typeof(UInt16) == columnType || typeof(UInt32) == columnType || typeof(UInt64) == columnType)
	        {
        		 return "INTEGER";
	        }
            else if (typeof(double) == columnType || typeof(float) == columnType)
            {
                return "DOUBLE";
            }
            else if (typeof(DateTime) == columnType || typeof(Decimal) == columnType)
            {
                return "DATETIME";
            }
            else if (typeof(Boolean) == columnType)
            {
                return "SMALLINT";
            }
            else if (typeof(string) == columnType)
            {
                return "VARCHAR (255)";
            }
            else
            {
                return "NONE";
            }
        }
    }
}
